package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-14
 * Time: 下午4:51
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CwsfcDao extends SimpleHibernateTemplate {
    /**
     * 财务收发存报表
     *
     * @param gsid
     * @param year
     * @param month
     * @return
     */
    public Map<String, List> cwsfcList(Long gsid, Integer year, Integer month) {
        if (gsid == null)
            gsid = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_SFC(?,?,?,?)}");
            call.setLong(1, gsid);
            call.setInt(2, year);
            call.setInt(3, month);
            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(4);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, List> result = new HashMap<String, List>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if ("1".equals(r.get("flag").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 销售成本结转
     *
     * @param year
     * @param month
     * @return
     */
    public String cbjz(Integer year, Integer month) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call UPDATE_CW_XSCBJZ(?,?,?)}");
            call.setInt(1, year);
            call.setInt(2, month);
            call.registerOutParameter(3, Types.VARCHAR);
            call.execute();
            //====================================
            String errorMsg = call.getString(3);
            return errorMsg;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 财务收发存月末结转
     *
     * @param year
     * @param month
     * @return
     */
    public String sfcymjz(Integer year, Integer month) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call SAVE_CW_SFC(?,?,?)}");
            call.setInt(1, year);
            call.setInt(2, month);
            call.registerOutParameter(3, Types.VARCHAR);
            call.execute();
            //====================================
            String errorMsg = call.getString(3);
            return errorMsg;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 财务收发存明细
     *
     * @param gsid
     * @param type      CGJXFP 采购进项发票  WLJXFP物流进项发票 CGJXGS_采购进项估收 WLJXGS物流进项估收 SLTZ 数量调整 XSFP_销项发票 NBLL内部领料
     * @param startDate
     * @param endDate
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Map<String, Object> cwsfcDetailList(Long gsid, String type, Integer codeId, java.util.Date startDate, java.util.Date endDate,
                                               Integer pageNo, Integer pageSize) {
        if (codeId == null)
            codeId = -1;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        ResultSet frs = null;
        Integer total = 0;
        if (startDate == null)
            startDate = new java.util.Date();
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_SFC_DETAIL(?,?,?,?,?,?,?,?,?,?)}");
            call.setLong(1, gsid);
            call.setString(2, type);
            call.setInt(3, codeId);
            call.setInt(4, (pageNo - 1) * pageSize);
            call.setInt(5, pageNo * pageSize);
            call.setDate(6, new Date(startDate.getTime()));
            call.setDate(7, new Date(endDate.getTime()));
            call.registerOutParameter(8, Types.NUMERIC);
            call.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);
            call.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            total = call.getInt(8);
            rs = ((OracleCallableStatement) call).getCursor(9);
            frs = ((OracleCallableStatement) call).getCursor(10);

            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();

            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                rows.add(r);
            }

            resultsMetaData = frs.getMetaData();
            columnCount = resultsMetaData.getColumnCount();
            while (frs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, frs.getObject(i));
                }
                footer.add(r);
            }

            result.put("total", total);
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }


    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    /**
     * 采购单统计明细
     *
     * @param codeId
     * @param gsId
     * @param jsStart
     * @param jsEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findCgdTjMx(Integer codeId, Long gsId, java.util.Date jsStart, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",gg.code \"wzbm\",gg.name \"wzname\",gg.ggxh \"gg\",gg.dw \"dw\",mx.rksl \"rksl\"," +
                " mx.jshsj \"jshsj\",mx.jsbhsj \"jsbhsj\",nvl(mx.jsbhse,0) + nvl(mx.jsse,0) \"hse\",mx.jsse \"se\",mx.jsbhse \"jsbhse\",gys.jc \"gys\"";
        String from = " from t_sys_gg gg" +
                "  left join t_sys_pm_cy pmkm on(pmkm.pm_id = gg.pm_id)" +
                "  left join t_biz_cgdmx mx on(mx.gg_id = gg.id)" +
                "  left join t_biz_cgd cgd on(cgd.id = mx.cgd_id)" +
                "  left join t_sys_gsjg gys on(gys.id = cgd.gys_id) " +
                "  left join t_biz_jsd jsd on(jsd.id = mx.cgjs_id and jsd.deleted = 0)" +
                "  left join t_sys_gsjg gs on(cgd.cggs_id = gs.id)" +
                " where cgd.deleted=0 and mx.deleted = 0 and jsd.deleted=0 and jsd.shzt=1 and jsd.type=0 ";
        String where = "";
        if (codeId != null) {
            where += " and pmkm.codeid = :codeId";
        }
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsStart != null) {
            where += " and jsd.kprq >= :dateStart";
        }
        if (jsEnd != null) {
            where += " and jsd.kprq < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.cgdh");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select nvl(sum(mx.jsbhse),0)" + from + where);
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsStart != null) {
            sqlQuery.setDate("dateStart", jsStart);
            countQuery.setDate("dateStart", jsStart);
            sumQuery.setDate("dateStart", jsStart);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", sumQuery.uniqueResult());
        map.put("page", page);
        return map;
    }


    /**
     * 采购单估收统计明细
     *
     * @param codeId
     * @param gsId
     * @param jsEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findCgdGsTjMx(Integer codeId, Long gsId, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",gg.code \"wzbm\",gg.name \"wzname\",gg.ggxh \"gg\",gg.dw \"dw\",mx.rksl \"rksl\"," +
                " mx.hsj \"hsj\",mx.bhsj \"bhsj\", nvl(mx.rksl*mx.hsj,0) \"hse\",nvl(mx.rksl*mx.hsj,0)-nvl(mx.rksl*mx.bhsj,0) \"se\",nvl(mx.rksl*mx.bhsj,0) \"bhse\",gys.jc \"gys\"";
        String from = " from t_sys_gg gg" +
                "  left join t_sys_pm_cy pmkm on(gg.pm_id = pmkm.pm_id)" +
                "  left join t_biz_cgdmx mx on(mx.gg_id = gg.id)" +
                "  left join t_biz_cgd cgd on(cgd.id = mx.cgd_id and cgd.deleted = 0)" +
                "  left join t_sys_gsjg gs on(cgd.cggs_id = gs.id)" +
                "  left join t_sys_gsjg gys on(gys.id = cgd.gys_id) " +
                " where not exists (select 1 from t_biz_jsd jsd where jsd.id=mx.cgjs_id and jsd.deleted=0 and jsd.shzt=1 and jsd.kprq<:dateEnd) ";
        String where = "";
        if (codeId != null) {
            where += " and pmkm.codeid = :codeId";
        }
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsEnd != null) {
            where += " and cgd.rksj < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.cgdh");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(nvl(mx.rksl*mx.bhsj,0))" + from + where);
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", sumQuery.uniqueResult());
        map.put("page", page);
        return map;
    }

    /**
     * 物流结算明细
     *
     * @param codeId
     * @param gsId
     * @param jsStart
     * @param jsEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findWljsMx(Integer codeId, Long gsId, java.util.Date jsStart, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);

        String t = "select gs.jc gsjc, cgd.cgdh  cgdh, sydw.jc sydwjc, cyfy.fylb fylb,fy.jfsl jfsl, " +
                "       fy.je je,fy.kdse kdse, fy.jsje jinge " +
                "  from t_biz_glfy fy  left join t_biz_cgd cgd on (fy.cgd_id = cgd.id and cgd.deleted = 0) " +
                "  left join t_sys_gsjg gs on (cgd.cggs_id = gs.id) " +
                "  left join t_sys_gsjg sydw on (sydw.id = fy.sydw_id) " +
                "  left join t_biz_jsd js on js.id = fy.wljsd_id and js.deleted = 0 " +
                "  left join t_sys_cyfyys cyfy on (fy.cyfy_fylb_id = cyfy.id) " +
                " where fy.deleted = 0 and fy.csh is null and fy.cgd_id is not null and fy.xsd_id is null and fy.wljsd_id is not null" +
                " and exists (select 1 from t_biz_jsd jsd where jsd.id=fy.wljsd_id and jsd.deleted=0 and jsd.type = 2 and jsd.shzt=1 and jsd.kprq < :dateEnd and jsd.kprq >= :dateStart) " +
                (codeId == null ? "" : " and exists (select mx.id from t_biz_glfymx mx left join t_sys_gg g on g.id = mx.gg_id left join t_sys_pm_cy ys on ys.pm_id = g.pm_id where mx.glfy_id = fy.id and ys.codeid = :codeId) ") +
                (gsId == null ? "" : " and gs.id = :gsId ") + (jsEnd != null ? " and js.kprq < :dateEnd" : " ") + " and js.kprq >= :dateStart" +
                " union all  " +
                " select gs.jc gsjc, fy.gldh  cgdh, sydw.jc   sydwjc, cyfy.fylb fylb, fy.jfsl jfsl, " +
                "  fy.je je,fy.kdse kdse,fy.jsje jinge " +
                "  from t_biz_glfy fy left join t_sys_gsjg gs on gs.id = fy.createorgan_id " +
                "  left join t_sys_cyfyys cyfy on (fy.cyfy_fylb_id = cyfy.id) " +
                "  left join t_sys_gsjg sydw on (sydw.id = fy.sydw_id) " +
                "  left join t_biz_jsd js on js.id = fy.wljsd_id and js.deleted = 0" +
                " where fy.csh = 1 and fy.type = 2 and exists (select 1 from t_biz_jsd jsd where jsd.id=fy.wljsd_id and jsd.deleted=0 and jsd.shzt=1 and jsd.kprq< :dateEnd and jsd.kprq >= :dateStart)  " +
                (codeId == null ? "" : " and exists (select mx.id from t_biz_glfymx mx left join t_sys_gg g on g.id = mx.gg_id left join t_sys_pm_cy ys on ys.pm_id = g.pm_id where mx.glfy_id = fy.id and ys.codeid = :codeId) ") +
                (gsId == null ? "" : " and gs.id = :gsId ") + (jsEnd != null ? " and js.kprq < :dateEnd" : "") + " and js.kprq >= :dateStart";

        SQLQuery sqlQuery = getSession().createSQLQuery(" select gsjc \"gsjc\", cgdh  \"cgdh\", sydwjc \"sydwjc\", fylb \"fylb\", jfsl \"jfsl\", " +
                "  je \"je\",kdse \"kdse\",jinge \"jinge\" " + " from (" + t + ") order by cgdh ");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1) from (" + t + ")");
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(nvl(jinge,0)) from (" + t + ")");
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (jsStart != null) {
            sqlQuery.setDate("dateStart", jsStart);
            countQuery.setDate("dateStart", jsStart);
            sumQuery.setDate("dateStart", jsStart);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", sumQuery.uniqueResult());
        map.put("page", page);
        return map;
    }

    /**
     * 物流估收明细
     *
     * @param codeId
     * @param gsId
     * @param jsEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findWlgsMx(Integer codeId, Long gsId, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);

        String t = "select gs.jc gsjc, cgd.cgdh  cgdh, sydw.jc sydwjc, cyfy.fylb fylb,fy.jfsl jfsl, " +
                "       fy.je je,fy.kdse kdse, fy.me jinge " +
                "  from t_biz_glfy fy  left join t_biz_cgd cgd on (fy.cgd_id = cgd.id and cgd.deleted = 0) " +
                "  left join t_sys_gsjg gs on (cgd.cggs_id = gs.id) " +
                "  left join t_sys_gsjg sydw on (sydw.id = fy.sydw_id) " +
                "  left join t_sys_cyfyys cyfy on (fy.cyfy_fylb_id = cyfy.id) " +
                " where fy.deleted = 0 and fy.csh is null and fy.cgd_id is not null and fy.xsd_id is null and cgd.yrk=1" +
                " and not exists (select 1 from t_biz_jsd jsd where jsd.id=fy.wljsd_id and jsd.deleted=0 and jsd.shzt=1 and jsd.kprq< :dateEnd) " +
                (codeId == null ? "" : " and exists (select mx.id from t_biz_glfymx mx left join t_sys_gg g on g.id = mx.gg_id " +
                        "left join t_sys_pm_cy ys on ys.pm_id = g.pm_id " +
                        "where mx.glfy_id = fy.id and ys.codeid = :codeId) ") +
                (gsId == null ? "" : " and gs.id = :gsId ") + (jsEnd != null ? " and cgd.rksj < :dateEnd" : "") +
                " union all  " +
                " select gs.jc gsjc, fy.gldh  cgdh, sydw.jc   sydwjc, cyfy.fylb fylb, fy.jfsl jfsl, " +
                "  fy.je je,fy.kdse kdse,fy.me jinge " +
                "  from t_biz_glfy fy left join t_sys_gsjg gs on gs.id = fy.createorgan_id " +
                "  left join t_sys_cyfyys cyfy on (fy.cyfy_fylb_id = cyfy.id) " +
                "  left join t_sys_gsjg sydw on (sydw.id = fy.sydw_id) " +
                " where fy.deleted = 0 and fy.csh = 1 and fy.type = 2 and not exists (select 1 from t_biz_jsd jsd where jsd.id=fy.wljsd_id and jsd.deleted=0 and jsd.shzt=1 and jsd.kprq< :dateEnd)  " +
                (codeId == null ? "" : " and exists (select mx.id from t_biz_glfymx mx left join t_sys_gg g on g.id = mx.gg_id left join t_sys_pm_cy ys on ys.pm_id = g.pm_id where mx.glfy_id = fy.id and ys.codeid = :codeId) ") +
                (gsId == null ? "" : " and gs.id = :gsId ") + (jsEnd != null ? " and fy.date_created < :dateEnd" : "");

        SQLQuery sqlQuery = getSession().createSQLQuery(" select gsjc \"gsjc\", cgdh  \"cgdh\", sydwjc \"sydwjc\", fylb \"fylb\", jfsl \"jfsl\", " +
                "  je \"je\",kdse \"kdse\",jinge \"jinge\" " + " from (" + t + ") order by cgdh ");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1) from (" + t + ")");
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(nvl(je,0)) from (" + t + ")");
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", sumQuery.uniqueResult());
        map.put("page", page);
        return map;
    }

    /**
     * 销售结算明细
     *
     * @param codeId
     * @param gsId
     * @param jsStart
     * @param jsEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findXsjsMx(Integer codeId, Long gsId, java.util.Date jsStart, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gs\",xsd.xsdh \"xsdh\",gg.code \"wzcode\",gg.name \"wzbm\",gg.ggxh \"ggxh\",gg.dw \"dw\",round(mx.ck_sbsl,3) \"cksl\"," +
                " round(nvl(mx.cbje,0),2) \"cnje\",decode(mx.ck_sbsl,0,0,round(mx.jsbhse/mx.ck_sbsl,4)) \"xsdj\",round(nvl(mx.jsbhse,0),2) \"je\",round(mx.jsse,2) \"se\",kh.jc \"kh\" ";
        String from = "from t_biz_xsmx mx" +
                "  left join t_sys_gg gg on(gg.id = mx.jtwz_id)" +
//                "  left join t_sys_pm_cy pmkm on(pmkm.pm_id = gg.pm_id)" +
                "  left join t_biz_xsd xsd on (xsd.id = mx.xsd_id and xsd.deleted = 0)" +
                "  left join t_biz_jsd jsd on (mx.xsjsd_id = jsd.id and jsd.deleted = 0)" +
                "  left join t_sys_gsjg gs on (gs.id = xsd.xsgs_id) " +
                "  left join t_sys_gsjg kh on (kh.id = jsd.glqy_id) " +
                "  where mx.deleted = 0 " +
                "  and exists (select 1 from t_biz_jsd jsd where jsd.id = mx.xsjsd_id and jsd.type=1 and jsd.deleted=0 " +
                "         and jsd.shzt=1 and jsd.kprq< :dateEnd and jsd.kprq >= :dateStart) ";
        String where = "";
//        if (codeId != null) {
//            where += " and pmkm.codeid = :codeId ";
//        }
        if (gsId != null) {
            where += " and gs.id = :gsId ";
        }
        if (jsStart != null) {
            where += " and jsd.date_created >= :dateStart ";
        }
        if (jsEnd != null) {
            where += " and jsd.date_created < :dateEnd ";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by xsd.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(nvl(mx.cbje,0)) as \"cnje\",sum(nvl(mx.jsbhse,0)) as \"je\"" + from + where);
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsStart != null) {
            sqlQuery.setDate("dateStart", jsStart);
            countQuery.setDate("dateStart", jsStart);
            sumQuery.setDate("dateStart", jsStart);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", (sumQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).uniqueResult()));
        map.put("page", page);
        return map;
    }

    /**
     * 领料明细
     *
     * @param codeId
     * @param gsId
     * @param dateStart
     * @param dateEnd
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Deprecated
    public Map findLlMx(Integer codeId, Long gsId, java.util.Date dateStart, java.util.Date dateEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select lld.lldh \"lldh\",lld.date_created \"kdrq\",llgs.jc \"llgs\",llbm.jc \"llbm\",llr.xm \"llr\"," +
                " ck.mc \"ck\",gg.code \"ggcode\",gg.name \"ggname\",gg.ggxh \"ggxh\",gg.th \"th\",gg.dw \"dw\"," +
                " mx.flsl \"flsl\",nvl(mx.cbje,0) \"flje\",flgs.jc \"flgs\",flbm.jc \"flbm\",flr.xm \"flr\",zdr.xm \"zdr\"";
        String from = "from t_biz_lldmx mx left join t_biz_lld lld on (lld.id = mx.lld_id and lld.deleted = 0) " +
                "  left join t_biz_kh kh on(kh.id = mx.kh_id)" +
                "  left join t_sys_gg gg on(gg.id = kh.jtwz_id)" +
                "  left join t_sys_pm_cy pmkm on(pmkm.pm_id = gg.pm_id)" +
                "  left join t_sys_gsjg llgs on(lld.llgs_id = llgs.id)" +
                "  left join t_sys_gsjg llbm on(lld.llbm_id = llbm.id)" +
                "  left join t_sys_yg llr on(lld.llr_id = llr.id)" +
                "  left join t_sys_gsjg flgs on(lld.flgs_id = flgs.id)" +
                "  left join t_sys_gsjg flbm on(lld.flbm_id = flbm.id)" +
                "  left join t_sys_yg flr on(lld.flr_id = flr.id)" +
                "  left join t_sys_ck ck on(lld.ck_id = ck.id)" +
                "  left join t_sys_yg zdr on(lld.createuser_id = zdr.id)" +
                " where mx.deleted = 0 and lld.deleted=0 and kh.deleted=0 and lld.zt = 'yfl'";
        String where = "";
        if (codeId != null) {
            where += " and pmkm.codeid = :codeId";
        }
        if (gsId != null) {
            where += " and flgs.id = :gsId";
        }
        if (dateStart != null) {
            where += " and lld.flrq >= :dateStart";
        }
        if (dateEnd != null) {
            where += " and lld.flrq < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by lld.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(nvl(mx.cbje,0)) as \"je\"" + from + where);
        if (codeId != null) {
            sqlQuery.setLong("codeId", codeId);
            countQuery.setLong("codeId", codeId);
            sumQuery.setLong("codeId", codeId);
        }
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (dateStart != null) {
            sqlQuery.setDate("dateStart", dateStart);
            countQuery.setDate("dateStart", dateStart);
            sumQuery.setDate("dateStart", dateStart);
        }
        if (dateEnd != null) {
            sqlQuery.setDate("dateEnd", dateEnd);
            countQuery.setDate("dateEnd", dateEnd);
            sumQuery.setDate("dateEnd", dateEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        sumQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", (Map) sumQuery.uniqueResult());
        map.put("page", page);
        return map;
    }

}
